package pers.ht.mbpo.dao;


import pers.ht.mbpo.bean.BusCar;
import pers.ht.mbpo.bean.BusCarReturn;
import pers.ht.mbpo.bean.Station;
import pers.ht.mbpo.util.DBUtil;
import pers.ht.mbpo.util.DaoUtil;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class BusCarDao {
    //添加
    public void insert(BusCar c) {
        String sql = "INSERT INTO bus_car(cartime,carid,busLineName,updown,keypoint,pointName,pointLng,pointLat,distanceNext,flash,nextid,interv) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
        DaoUtil.update(sql, c.getTime(), c.getCarid(), c.getBusLineName(), c.getUpdown(), c.getKeypoint(), c.getPointName(), c.getPointLng(), c.getPointLat(), c.getDistanceNext(), c.getFlash(), c.getNextid(), c.getInterval());
    }
    //修改
    public void update(BusCar c) {
        String sql = "update bus_car set cartime=?,flash=?,interv=?where carid=? and keypoint=?";
        DaoUtil.update(sql, c.getTime(),c.getFlash(),c.getInterval(),c.getCarid(), c.getKeypoint());
    }

    //删除
    public void delete(String carid) {
        String sql = "DELETE FROM bus_car where carid='"+carid+"';";
        DaoUtil.update(sql);
    }

    //查询所有
    public List<BusCarReturn> query(String time, String busLineName, String updown,String carid) {
        List<BusCarReturn> busCars = new ArrayList<BusCarReturn>();
        String sqlcar = "SELECT distinct carid,buslineName,updown from bus_car WHERE 1=1 ";
        if (time != null) {
            sqlcar += " AND cartime >= '" + time + "' ";
        }
        if (carid != null) {
            sqlcar += " AND carid = '" + carid + "' ";
        }
        if (busLineName != null) {
            try {
                String strB = new String(busLineName.getBytes("ISO-8859-1"), "UTF-8");
                sqlcar += " AND busLineName = '" + strB + "' ";
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
        }
        if (updown != null) {
            sqlcar += " AND updown = '" + updown + "' ";
        }



        Connection conn = null;
        Statement stat = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            try {
                stat = conn.createStatement();

            } catch (SQLException e) {
                e.printStackTrace();
            }
            rs = stat.executeQuery(sqlcar);
            while (rs.next()) {
                BusCarReturn c = new BusCarReturn();
                c.setCarid(rs.getString("carid"));
                c.setBusLineName(rs.getString("busLineName"));
                c.setUpdown(rs.getString("updown"));
                Connection conn1 = null;
                Statement stat1 = null;
                ResultSet rs2 = null;
                conn1 = DBUtil.getConnection();
                stat1 = conn1.createStatement();
                String sql = "SELECT c.*,k.* from bus_car c left outer JOIN bus_edge_keypoint k ON c.nextid=k.keypoint AND c.busLineName=k.busLineName And c.updown=k.updown WHERE 1=1 ";
                if (time != null) {
                    sql += " AND c.cartime >= '" + time + "' ";
                }
                if (busLineName != null) {
                    try {
                        String strB = new String(busLineName.getBytes("ISO-8859-1"), "UTF-8");
                        sql += " AND c.busLineName = '" + strB + "' ";
                    } catch (UnsupportedEncodingException e) {
                        e.printStackTrace();
                    }
                }
                if (updown != null) {
                    sql += " AND c.updown = '" + updown + "' ";
                }
                sql += " AND c.carid = '" + c.getCarid() + "' ORDER BY c.cartime";
                rs2 = stat1.executeQuery(sql);
                while (rs2.next()) {
                    Station s = new Station();
                    s.setTime(rs2.getString("cartime"));
                    s.setKeypoint(rs2.getString("c.keypoint"));
                    s.setPointName(rs2.getString("pointName"));
                    s.setPointLng(rs2.getString("pointLng"));
                    s.setPointLat(rs2.getString("pointLat"));
                    s.setDistanceNext(rs2.getString("distanceNext"));
                    s.setFlash(rs2.getString("flash"));
                    s.setInterval(rs2.getString("interv"));
                    s.setLnglat(rs2.getString("lnglat"));
                    c.getStations().add(s);
                }
                busCars.add(c);
                DBUtil.close(rs2, stat1, conn1);
            }

            DBUtil.close(rs, stat, conn);
        } catch (
                SQLException e1
                )

        {
            e1.printStackTrace();
        }

        return busCars;
    }

    //查询所有第一次出现车辆信息
    public List<BusCarReturn> queryFirst(String time, String busLineName, String updown) {
        List<BusCarReturn> busCars = new ArrayList<BusCarReturn>();
        String sqlcar = "SELECT distinct carid,buslineName,updown from bus_car WHERE 1=1 ";
        if (time != null) {
            sqlcar += " AND cartime = '" + time + "' ";
        }
        if (busLineName != null) {
            try {
                String strB = new String(busLineName.getBytes("ISO-8859-1"), "UTF-8");
                sqlcar += " AND busLineName = '" + strB + "' ";
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
        }
        if (updown != null) {
            sqlcar += " AND updown = '" + updown + "' ";
        }



        Connection conn = null;
        Statement stat = null;
        ResultSet rs= null;
        try {
            conn = DBUtil.getConnection();
            try {
                stat = conn.createStatement();

            } catch (SQLException e) {
                e.printStackTrace();
            }
            rs = stat.executeQuery(sqlcar);
            while (rs.next()) {
                Connection conn1 = null;
                Statement stat1 = null;
                ResultSet rs1 = null;
                conn1 = DBUtil.getConnection();
                stat1 = conn1.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
                String sqlfirst= "SELECT cartime from bus_car WHERE carid='"+rs.getString("carid")+"'  limit 1";
                rs1=stat1.executeQuery(sqlfirst);
                if(rs1.first()){
                    String temp=rs1.getString("cartime");
                    if(!rs1.getString("cartime").equals(time)){
                        DBUtil.close(rs1, stat1, conn1);
                        continue;
                    }else{
                        DBUtil.close(rs1, stat1, conn1);
                    }
                }
                BusCarReturn c = new BusCarReturn();
                c.setCarid(rs.getString("carid"));
                c.setBusLineName(rs.getString("busLineName"));
                c.setUpdown(rs.getString("updown"));
                Connection conn2 = null;
                Statement stat2 = null;
                ResultSet rs2 = null;
                conn2 = DBUtil.getConnection();
                stat2 = conn2.createStatement();
 //               String sql = "SELECT c.*,k.* from bus_car c left outer JOIN bus_edge_keypoint k ON c.nextid=k.keypoint AND c.busLineName=k.busLineName And c.updown=k.updown WHERE 1=1 ";
                String sql = "SELECT * from bus_car c WHERE 1=1 ";
                if (time != null) {
                    sql += " AND c.cartime >= '" + time + "' ";
                }
                if (busLineName != null) {
                    try {
                        String strB = new String(busLineName.getBytes("ISO-8859-1"), "UTF-8");
                        sql += " AND c.busLineName = '" + strB + "' ";
                    } catch (UnsupportedEncodingException e) {
                        e.printStackTrace();
                    }
                }
                if (updown != null) {
                    sql += " AND c.updown = '" + updown + "' ";
                }
                sql += " AND c.carid = '" + c.getCarid() + "' ORDER BY c.cartime";
                rs2 = stat2.executeQuery(sql);
                while (rs2.next()) {
                    Station s = new Station();
                    s.setTime(rs2.getString("cartime"));
                    s.setKeypoint(rs2.getString("c.keypoint"));
                    s.setPointName(rs2.getString("pointName"));
                    s.setPointLng(rs2.getString("pointLng"));
                    s.setPointLat(rs2.getString("pointLat"));
                    s.setDistanceNext(rs2.getString("distanceNext"));
                    s.setFlash(rs2.getString("flash"));
                    s.setInterval(rs2.getString("interv"));
                    String str=rs2.getString("nextid");
                    s.setNextid(str);
                    String sql2="select * from bus_edge_keypoint where keypoint='"+str+"' and busLineName='"+c.getBusLineName()+"' and updown='"+c.getUpdown()+"'";
                    Connection conn3 = null;
                    Statement stat3 = null;
                    ResultSet rs3 = null;
                    conn3 = DBUtil.getConnection();
                    stat3 = conn3.createStatement();
                    rs3=stat3.executeQuery(sql2);
                    if(rs3.next())
                        s.setLnglat(rs3.getString("lnglat"));
                    else
                        s.setLnglat("");
                    DBUtil.close(rs3, stat3, conn3);
                   // s.setLnglat(rs2.getString("lnglat"));
                    c.getStations().add(s);
                }
                busCars.add(c);
                DBUtil.close(rs2, stat2, conn2);
            }

            DBUtil.close(rs, stat, conn);
        } catch (
                SQLException e1
                )

        {
            e1.printStackTrace();
        }

        return busCars;
    }
}
